package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.GuardrailDto;
import com.hualude.roadsign.dto.GuardrailFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

import static com.hualude.roadsign.service.impl.CountServiceImpl.convertParams;

/**
 * Created by syk on 2017/11/21.
 */
public class GuardrailFaultCountRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<GuardrailFaultDto> getFault4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select p.guardrailMileage,f.GuardrailFaultId,f.GuardrailId,f.GuardrailFaultPhoto,p.GuardrailPhoto,f.GuardrailFaultLevel,f.GuardrailCheck,f.GuardrailMainCheck,\n" +
                "f.GuardrailFaultTime,f.GuardrailCheckTime,o.OrgName AS guardrailOrgName,o2.orgName as guardrailMainOrgName,p.GuardrailPeriod," +
                " p.GuardrailStartRoad,p.GuardrailEndRoad,p.GuardrailRoadName,p.GuardrailServiceLife,p.GuardrailType,p.GuardrailMaterial,\n" +
                "p.GuardrailArea,p.GuardrailCity,p.GuardrailProvince,u.UserName AS guardrailFaultCreatorName\n" +
                "from tb_guardrail_fault f,tb_org o,tb_org o2,tb_guardrail p,tb_user u\n" +
                "where f.GuardrailOrgId = o.OrgId and p.GuardrailMainOrgId=o2.orgId and f.GuardrailId = p.GuardrailId  AND\n" +
                "f.GuardrailFaultCreatorId = u.UserId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.GuardrailFaultTime between '" + dateStr[0] + "' and '" + dateStr[1] + 1 + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.GuardrailOrgId IN (" + orgIds + ") or " +
                    "f.GuardrailMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY f.GuardrailFaultTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        List<GuardrailFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getFaultCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_guardrail f,tb_org o WHERE f.GuardrailOrgId= o.OrgId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.GuardrailFaultTime between '" + dateStr[0] + "' and '" + dateStr[1] + 1 + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.GuardrailOrgId IN (" + orgIds + ") or " +
                    "f.GuardrailMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<GuardrailFaultDto> getDevice4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select s.guardrailMileage, m.GuardrailFaultId,m.GuardrailFaultPhoto,m.GuardrailMaintenancePhoto,m.GuardrailMaintenanceStatus,m.GuardrailMaintenanceTime,GuardrailMoney,\n" +
                "o.OrgName AS guardrailOrgName,m.GuardrailId,s.GuardrailEndRoad,s.GuardrailStartRoad,s.GuardrailArea,s.GuardrailRoadName,s.GuardrailServiceLife,s.GuardrailType,s.GuardrailMaterial,\n" +
                "s.GuardrailCity,s.GuardrailProvince,o2.orgName as guardrailMainOrgName,s.GuardrailPhoto,s.GuardrailPeriod,u.UserName AS guardrailFaultCreatorName\n" +
                "from tb_guardrail_fault m,tb_user u,tb_org o,tb_org o2,tb_guardrail s \n" +
                "where m.GuardrailMaintenanceStatus=2 and m.GuardrailMaintainerId = u.userId and s.GuardrailMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.GuardrailId = s.GuardrailId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.GuardrailMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.GuardrailMaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailFaultDto.class));
        List<GuardrailFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getDeviceCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select count(*) from tb_guardrail_fault m,tb_user u,tb_org o,tb_org o2,tb_guardrail s \n" +
                "where m.GuardrailMaintenanceStatus=2 and m.GuardrailMaintainerId = u.userId and s.GuardrailMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.GuardrailId = s.GuardrailId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.GuardrailMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public Integer getGuardrailCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_guardrail where GuardrailId like '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and GuardrailProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and GuardrailCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and GuardrailArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and GuardrailRoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (GuardrailStartRoad ='" + sQuery[4] + "' or GuardrailEndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (GuardrailEndRoad ='" + sQuery[5] + "' or GuardrailStartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((GuardrailStartRoad ='" + sQuery[4] + "' and GuardrailEndRoad ='" + sQuery[5] + "') or" +
                    "(GuardrailStartRoad = '" + sQuery[5] + "' and GuardrailEndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and GuardrailMainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and GuardrailOrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (GuardrailOrgId IN (" + orgIds + ") or " +
                    "GuardrailMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<GuardrailDto> getGuardrail4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select c.GuardrailId,c.GuardrailPhoto,c.GuardrailProvince,c.GuardrailCity,c.GuardrailArea," +
                "c.GuardrailRoadName,e.OrgName as guardrailMainOrgName,o.OrgName as guardrailOrgName," +
                "c.guardrailType,c.guardrailMaterial,c.GuardrailInstallTime,c.GuardrailStartRoad," +
                "c.GuardrailEndRoad,c.GuardrailIsComplete from tb_guardrail c " +
                "LEFT JOIN tb_org e ON c.GuardrailMainOrgId = e.OrgId " +
                "LEFT JOIN tb_org o ON c.GuardrailOrgId = o.OrgId " +
                "where c.GuardrailId LIKE '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:道路 4:起始路段 5:终止路段 6:主管单位 7:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and GuardrailProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and GuardrailCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and GuardrailArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1")) {
            hql += " and GuardrailRoadName ='" + sQuery[3] + "'";
        }
        if (!sQuery[4].equals("-1") && sQuery[5].equals("-1")) {
            hql += " and (GuardrailStartRoad ='" + sQuery[4] + "' or GuardrailEndRoad ='" + sQuery[4] + "')";
        }
        if (sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and (GuardrailEndRoad ='" + sQuery[5] + "' or GuardrailStartRoad ='" + sQuery[5] + "')";
        }
        if (!sQuery[4].equals("-1") && !sQuery[5].equals("-1")) {
            hql += " and ((GuardrailStartRoad ='" + sQuery[4] + "' and GuardrailEndRoad ='" + sQuery[5] + "') or" +
                    "(GuardrailStartRoad = '" + sQuery[5] + "' and GuardrailEndRoad = '" + sQuery[4] + "'))";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and GuardrailMainOrgId = '" + sQuery[6] + "'";
        }
        if (!sQuery[7].equals("-1")) {
            hql += " and GuardrailOrgId = '" + sQuery[7] + "'";
        }
        if (actor != 1) {
            hql += " and (GuardrailOrgId IN (" + orgIds + ") or " +
                    "GuardrailMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY c.GuardrailInstallTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(GuardrailDto.class));
        List<GuardrailDto> monitorDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return monitorDtos;
    }

    public List<String> getProvinceName(String orgIds) throws Exception {
        String hql = "select distinct c.GuardrailProvince from tb_guardrail c where c.GuardrailOrgId in (" + orgIds + ") or " +
                "c.GuardrailMainOrgId in (" + orgIds + ")";
        hql += " order by convert(c.GuardrailProvince using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> province = query.getResultList();
        return province;
    }

    public List<String> getCityName(String orgIds, String provinceName) throws Exception {
        String hql = "select distinct c.GuardrailCity from tb_guardrail c where (c.GuardrailOrgId in (" + orgIds + ") or " +
                "c.GuardrailMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and c.GuardrailProvince = '" + provinceName + "'";
        }
        hql += " order by convert(c.GuardrailCity using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> city = query.getResultList();
        return city;
    }

    public List<String> getAllAreaName(String orgIds, String provinceName, String cityName) throws Exception {
        String hql = "select distinct c.GuardrailArea from tb_guardrail c where (c.GuardrailOrgId IN (" + orgIds + ")" +
                " or c.GuardrailMainOrgId IN (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and c.GuardrailProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and c.GuardrailCity ='" + cityName + "'";
        }
        hql += " order by convert(c.GuardrailArea using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> area = query.getResultList();
        return area;
    }

    public List<String> getRoadNames(String provinceName, String cityName, String areaName, String orgId, Integer actor) throws Exception {
        String hql = "select distinct c.GuardrailRoadName from tb_guardrail c where (c.GuardrailOrgId in (" + orgId + ") or " +
                "c.GuardrailMainOrgId in (" + orgId + "))";
        if (!provinceName.equals("-1")) {
            hql += " and c.GuardrailProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and c.GuardrailCity ='" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and c.GuardrailArea = '" + areaName + "'";
        }
        hql += " order by convert(c.GuardrailRoadName using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getStartRoadName(String orgIds, String provinceName, String cityName, String areaName,
                                         String endRoadName, String roadName) throws Exception {
        String hql = "select distinct c.GuardrailStartRoad from tb_guardrail c where (c.GuardrailOrgId in (" + orgIds + ") or " +
                "c.GuardrailMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and c.GuardrailProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and c.GuardrailCity ='" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and c.GuardrailArea = '" + areaName + "'";
        }
        if (!endRoadName.equals("-1")) {
            hql += " and c.GuardrailEndRoad = '" + endRoadName + "'";
        }
        if (!roadName.equals("-1")) {
            hql += " and c.GuardrailRoadName = '" + roadName + "'";
        }
        hql += " order by convert(c.GuardrailStartRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getEndRoadName(String orgIds, String startRoad, String provinceName, String cityName,
                                       String areaName, String roadName) throws Exception {
        String hql = "select distinct c.GuardrailEndRoad from tb_guardrail c where (c.GuardrailOrgId in (" + orgIds + ") or " +
                "c.GuardrailMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and c.GuardrailProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and c.GuardrailCity ='" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and c.GuardrailArea = '" + areaName + "'";
        }
        if (!startRoad.equals("-1")) {
            hql += " and c.GuardrailStartRoad = '" + startRoad + "'";
        }
        if (!roadName.equals("-1")) {
            hql += " and c.GuardrailRoadName = '" + roadName + "'";
        }
        hql += " order by convert(c.GuardrailEndRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<Integer> getGuardrailTypeCounts(String orgIds, String time, String province, String city, String area, String road) throws Exception {
        String[] timeLimit = {"", ""};
        timeLimit = time.split(" - ");
        String hql = "select c.GuardrailType from tb_guardrail c  where (c.GuardrailOrgId in (" + orgIds + ") or " +
                "c.GuardrailMainOrgId in (" + orgIds + ")) AND (c.GuardrailInstallTime between '" + timeLimit[0] + "' and '" + timeLimit[1] + "') " +
                " and c.GuardrailProvince like '%" + province + "%' and c.GuardrailCity like '%" + city + "%' " +
                " and c.GuardrailArea like '%" + area + "%' and c.GuardrailRoadName like '%" + road + "%'   ORDER BY c.GuardrailType";
        Query query = em.createNativeQuery(hql);
        List<Integer> counts = query.getResultList();
        return counts;
    }
}
